﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using BE;
using BE.SearchCriteria;

namespace DAL
{
    public class ArticuloDAL : BaseDAL
    {

        public void Insertar(ArticuloBE pElemBE)
        {
            using (SqlConnection cnn = new SqlConnection(GetConnectionString()))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = cnn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "Articulos_i";
                    cmd.Parameters.Add(new SqlParameter("@Nombre", pElemBE.Nombre));
                    cmd.Parameters.Add(new SqlParameter("@Descripcion", pElemBE.Descripcion));
                    if (pElemBE.Rubro != null)
                        cmd.Parameters.Add(new SqlParameter("@IdRubro", pElemBE.Rubro.IdRubro));
                                        
                    //// Convertir imagen a MemoryStream
                    //System.IO.MemoryStream wImageStream = new System.IO.MemoryStream();
                    //pElemBE.Imagen.Save(wImageStream, System.Drawing.Imaging.ImageFormat.Jpeg);	 
                    //// Agregar imagen como parámetro
                    //SqlParameter wImgParameter = new SqlParameter("@Imagen", SqlDbType.Image);
                    //wImgParameter.Value = wImageStream.ToArray();
                    
                    SqlParameter wImgParameter = new SqlParameter("@Imagen", SqlDbType.Image);
                    wImgParameter.Value = pElemBE.Imagen;
                    cmd.Parameters.Add(wImgParameter);

                    cmd.Parameters.Add(new SqlParameter("@Activo", pElemBE.Activo));
                    cmd.Parameters.Add(new SqlParameter("@Precio", pElemBE.Precio));

                    SqlParameter p = new SqlParameter();
                    p.ParameterName = "@IdArticulo";
                    p.Direction = ParameterDirection.Output;
                    p.SqlDbType = SqlDbType.Int;
                    cmd.Parameters.Add(p);

                    cnn.Open();
                    cmd.ExecuteNonQuery();
                    pElemBE.IdArticulo = (Int32)cmd.Parameters["@IdArticulo"].Value;
                }
                catch (Exception)
                {
                    throw;
                }

            }
        }
        
        public void Modificar(ArticuloBE pElemBE)
        {
            using (SqlConnection cnn = new SqlConnection(GetConnectionString()))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = cnn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "Articulos_u";
                    cmd.Parameters.Add(new SqlParameter("@IdArticulo", pElemBE.IdArticulo));
                    cmd.Parameters.Add(new SqlParameter("@Nombre", pElemBE.Nombre));
                    cmd.Parameters.Add(new SqlParameter("@Descripcion", pElemBE.Descripcion));
                    if (pElemBE.Rubro != null)
                        cmd.Parameters.Add(new SqlParameter("@IdRubro", pElemBE.Rubro.IdRubro));

                    // Convertir imagen a MemoryStream
                    //System.IO.MemoryStream wImageStream = new System.IO.MemoryStream();
                    //pElemBE.Imagen.Save(wImageStream, System.Drawing.Imaging.ImageFormat.Jpeg);
                    //// Agregar imagen como parámetro
                    //SqlParameter wImgParameter = new SqlParameter("@Imagen", SqlDbType.Image);
                    //wImgParameter.Value = wImageStream.ToArray();

                    SqlParameter wImgParameter = new SqlParameter("@Imagen", SqlDbType.Image);
                    wImgParameter.Value = pElemBE.Imagen;
                    cmd.Parameters.Add(wImgParameter);

                    cmd.Parameters.Add(new SqlParameter("@Activo", pElemBE.Activo));
                    cmd.Parameters.Add(new SqlParameter("@Precio", pElemBE.Precio));

                    cnn.Open();
                    cmd.ExecuteNonQuery();                    
                }
                catch (Exception)
                {
                    throw;
                }

            }
        }
                
        public ArticuloBE SeleccionarPorId(int pId)
        {
            ArticuloBE oArticuloBE = null;
            using (SqlConnection cnn = new SqlConnection(GetConnectionString()))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = cnn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@IdArticulo", pId));
                cmd.CommandText = "Articulos_GetById";
                cnn.Open();
                SqlDataReader reader = null;
                reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    if (reader.Read())
                        oArticuloBE = ConstruirArticulo(reader, true);
                }

            }
            return oArticuloBE;
        }

        private ArticuloBE ConstruirArticulo(SqlDataReader reader, bool pCompleto)
        {
            ArticuloBE oArticuloBE = new ArticuloBE();
            Object[] values = new Object[reader.FieldCount];

            reader.GetValues(values);

            oArticuloBE.IdArticulo = Int32.Parse(reader["IdArticulo"].ToString());
            oArticuloBE.Nombre = reader["Nombre"].ToString();
            oArticuloBE.Descripcion = reader["Descripcion"].ToString();            
            oArticuloBE.Activo = Convert.ToBoolean(reader["Activo"]);
            oArticuloBE.Precio = Convert.ToDecimal(reader["Precio"]);

            if (reader["IdRubro"] != DBNull.Value)
            {
                oArticuloBE.Rubro = new RubroBE();
                oArticuloBE.Rubro.IdRubro = Int32.Parse(reader["IdRubro"].ToString());
                oArticuloBE.Rubro.Descripcion = reader["Rubro"].ToString();
            }

            if (pCompleto)
            {
                oArticuloBE.FechaAlta = Convert.ToDateTime(reader["FechaAlta"]);
                
                if (reader["Imagen"] != DBNull.Value)
                {
                    oArticuloBE.Imagen = (byte[])reader["Imagen"];
                }
            }

            return oArticuloBE;
        }

        public List<ArticuloBE> SeleccionarPorParametros(BaseSC pFiltroSC, int pPageSize, int? pPageNum, ref int pCountReg)
        {
            ArticuloSC wFiltroSC = (ArticuloSC)pFiltroSC;
            if (wFiltroSC == null)
                wFiltroSC = new ArticuloSC();

            List<ArticuloBE> listaDeArticuloBE = null;
            using (SqlConnection cnn = new SqlConnection(GetConnectionString()))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = cnn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@IdArticulo", wFiltroSC.IdArticulo));
                cmd.Parameters.Add(new SqlParameter("@Nombre", wFiltroSC.Nombre));
                cmd.Parameters.Add(new SqlParameter("@Descripcion", wFiltroSC.Descripcion));
                cmd.Parameters.Add(new SqlParameter("@Activo", wFiltroSC.Activo));
                cmd.Parameters.Add(new SqlParameter("@IdRubro", wFiltroSC.IdRubro));                
                cmd.Parameters.Add(new SqlParameter("@pagesize", pPageSize));
                cmd.Parameters.Add(new SqlParameter("@pagenum", pPageNum));

                SqlParameter p = new SqlParameter();
                p.ParameterName = "@PageCount";
                p.Direction = ParameterDirection.Output;
                p.SqlDbType = SqlDbType.Int;
                cmd.Parameters.Add(p);

                cmd.CommandText = "Articulos_GetAllPaginated";
                cnn.Open();
                SqlDataReader reader = null;
                reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    listaDeArticuloBE = new List<ArticuloBE>();

                    while (reader.Read())
                    {
                        ArticuloBE oArticuloBE = ConstruirArticulo(reader, false);

                        listaDeArticuloBE.Add(oArticuloBE);
                    }
                }
                reader.Close();
                pCountReg = (Int32)cmd.Parameters["@PageCount"].Value;

            }
            return listaDeArticuloBE;
        }

        public void Delete(int pIdArticulo)
        {
            try
            {
                using (SqlConnection cnn = new SqlConnection(GetConnectionString()))
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = cnn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "Articulos_d";
                    cmd.Parameters.Add(new SqlParameter("@IdArticulo", pIdArticulo));
                    cnn.Open();
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception)
            { throw; }

        }

        public List<ArticuloBE> SeleccionarArticulosPresentaciones(BaseSC pFiltroSC, int pPageSize, int? pPageNum, ref int pCountReg)
        {
            ArticuloSC wFiltroSC = (ArticuloSC)pFiltroSC;
            if (wFiltroSC == null)
                wFiltroSC = new ArticuloSC();

            List<ArticuloBE> listaDeArticuloBE = null;
            using (SqlConnection cnn = new SqlConnection(GetConnectionString()))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = cnn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@IdArticulo", wFiltroSC.IdArticulo));
                cmd.Parameters.Add(new SqlParameter("@Nombre", wFiltroSC.Nombre));
                cmd.Parameters.Add(new SqlParameter("@Descripcion", wFiltroSC.Descripcion));
                cmd.Parameters.Add(new SqlParameter("@Activo", wFiltroSC.Activo));
                cmd.Parameters.Add(new SqlParameter("@IdRubro", wFiltroSC.IdRubro));
                cmd.Parameters.Add(new SqlParameter("@pagesize", pPageSize));
                cmd.Parameters.Add(new SqlParameter("@pagenum", pPageNum));

                SqlParameter p = new SqlParameter();
                p.ParameterName = "@PageCount";
                p.Direction = ParameterDirection.Output;
                p.SqlDbType = SqlDbType.Int;
                cmd.Parameters.Add(p);

                cmd.CommandText = "ArticulosPrentaciones_GetAllPaginated";
                cnn.Open();
                SqlDataReader reader = null;
                reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    listaDeArticuloBE = new List<ArticuloBE>();

                    while (reader.Read())
                    {
                        ArticuloBE oArticuloBE = ConstruirArticuloPresentaciones(reader, false);

                        listaDeArticuloBE.Add(oArticuloBE);
                    }
                }
                reader.Close();
                pCountReg = (Int32)cmd.Parameters["@PageCount"].Value;

            }
            return listaDeArticuloBE;
        }


        private ArticuloBE ConstruirArticuloPresentaciones(SqlDataReader reader, bool pCompleto)
        {
            ArticuloBE oArticuloBE = new ArticuloBE();
            Object[] values = new Object[reader.FieldCount];

            reader.GetValues(values);

            oArticuloBE.IdArticulo = Int32.Parse(reader["IdArticulo"].ToString());
            oArticuloBE.Nombre = reader["Nombre"].ToString();
            oArticuloBE.Descripcion = reader["Descripcion"].ToString();
            oArticuloBE.Activo = Convert.ToBoolean(reader["Activo"]);
 
            if (reader["IdRubro"] != DBNull.Value)
            {
                oArticuloBE.Rubro = new RubroBE();
                oArticuloBE.Rubro.IdRubro = Int32.Parse(reader["IdRubro"].ToString());
                oArticuloBE.Rubro.Descripcion = reader["Rubro"].ToString();
            }

            if (pCompleto)
            {
                oArticuloBE.FechaAlta = Convert.ToDateTime(reader["FechaAlta"]);

                if (reader["Imagen"] != DBNull.Value)
                {
                    oArticuloBE.Imagen = (byte[])reader["Imagen"];
                }
            }

            oArticuloBE.Presentacion = new PresentacionBE();
            oArticuloBE.Presentacion.IdPresentacion = Int32.Parse(reader["IdPresentacion"].ToString());
            oArticuloBE.Presentacion.Descripcion = reader["Presentacion"].ToString();
            
            return oArticuloBE;
        }
    }
}
